package id.co.shuba.spamobile.data;

import id.co.shuba.spamobile.App;
import id.co.shuba.spamobile.Encryptor;
import id.co.shuba.spamobile.R;
import id.co.shuba.spamobile.Sessions;
import id.co.shuba.spamobile.model.UserInfo;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

	private static DatabaseHandler instance;

	private static final int DATABASE_VERSION = 6;
	private static final String DATABASE_NAME = "spamobile";

	public static final int INBOX_PIPELINE = 1;

	public static final boolean USE_LOCAL_DATA = false;

	private Context ctx;

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		ctx = context;
		//SQLiteDatabase db = getWritableDatabase();
		//db.execSQL("UPDATE TBL_PROSPECT_VISIT SET IS_DELETED = '0' WHERE length(IS_DELETED) > 2");
	}

	public static DatabaseHandler getInstance(Context context) {
		if (instance != null)
			return instance;

		return new DatabaseHandler(context);
	}
	
	public void purgeData(String date)
    {        
		SQLiteDatabase db = getWritableDatabase();
		String sql = 				
				 "DELETE FROM TBL_PROSPECT_VISIT " +
                 "WHERE PROSPECT_ID IN (SELECT PROSPECT_ID  " +
                        "FROM TBL_PROSPECT  " +
                   "WHERE strftime('%Y-%m-%d', UPDATE_DATE) < strftime('%Y-%m-%d', ?))";
		
		db.execSQL(sql, new String[] { date });
				                    
		sql = 				
				 "DELETE FROM TBL_PROSPECT_PROGRESS " +
                "WHERE PROSPECT_ID IN (SELECT PROSPECT_ID  " +
                       "FROM TBL_PROSPECT  " +
                  "WHERE strftime('%Y-%m-%d', UPDATE_DATE) < strftime('%Y-%m-%d', ?))";
		
		db.execSQL(sql, new String[] { date });

		sql = 
                "DELETE FROM TBL_PROSPECT "+ 
                "WHERE strftime('%Y-%m-%d', UPDATE_DATE) < strftime('%Y-%m-%d', ?)";
		
		db.execSQL(sql, new String[] { date });
                
		sql = 
               	"DELETE FROM TBL_MAINTENANCE_VISIT " + 
                "WHERE strftime('%Y-%m-%d', UPDATE_DATE) < strftime('%Y-%m-%d', ?)";

		db.execSQL(sql, new String[] { date });
		
		db.execSQL("VACUUM");                                   
    }
	
	public void truncateTransactions() {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("DELETE FROM TBL_MAINTENANCE_VISIT");
		db.execSQL("DELETE FROM TBL_PROSPECT_PROGRESS");
		db.execSQL("DELETE FROM TBL_PROSPECT_VISIT");
		db.execSQL("DELETE FROM TBL_PROSPECT");
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "CREATE TABLE TBL_PROSPECT ("
				+ "PROSPECT_ID TEXT PRIMARY KEY,"
				+ "PROSPECT_NO TEXT,"
				+ "FULL_NAME TEXT, JENIS_USAHA TEXT,"
				+ "RENCANA_PLAFON INT, MOBILE_PHONE TEXT,"
				+ "OFFICE_PHONE TEXT, RESIDENTIAL_PHONE TEXT,"
				+ "KODYA_KAB_BUSINESS TEXT, KECAMATAN_BUSINESS TEXT, KELURAHAN_BUSINESS TEXT,"
				+ "PROSPECT_TYPE TEXT, DEBTOR_TYPE TEXT,"
				+ "CHANNELING TEXT, ZIP_CODE_BUSINESS TEXT, ADDRESS_BUSINESS TEXT,"
				+ "EKSTERNAL TEXT, REFERENSI TEXT,"
				+ "AVG_EXPENSE INT,"
				+ "DATE_OF_BIRTH TEXT, MARITAL_STATUS TEXT,"
				+ "SPOUSE_NAME TEXT, JML_TANGGUNGAN INT, PRODUCT_CODE TEXT,"
				+ "KTP_NO TEXT, KTP_NAME TEXT, KTP_ADDRESS TEXT,"
				+ "PROGRESS_STATUS TEXT, INBOX INT,ENTRY_BY TEXT, ENTRY_DATE DATETIME,"
				+ "UPDATE_BY TEXT, UPDATE_DATE DATETIME, IS_UPLOAD TEXT,"
				+ "UPLOAD_BY TEXT, UPLOAD_DATE DATETIME, AO_CODE TEXT, UNIT_CODE TEXT,"
				+ "PIPELINE_START_TIME DATETIME, PIPELINE_FINISH_TIME DATETIME,"
				+ "PROSPECT_START_TIME DATETIME, PROSPECT_FINISH_TIME DATETIME,"
				+ "UM_DECISION TEXT, UM_DECISION_NOTE TEXT, UM_DECISION_DATE DATETIME, REPUTASI_DEBITUR TEXT,"
				+ "CO_ASSIGNED TEXT, ALTERNATE_FOR TEXT, NOT_INTERESTED_REASON TEXT,"
				+ "GU_CLOSE_DEAL DATETIME, IS_DELETED TEXT, IS_OFFLINE_ENTRY TEXT,"
				+ "OMZET_1 INT, OMZET_2 INT, OMZET_3 INT, OMZET_4 INT, OMZET_5 INT, OMZET_6 INT,"
				+ "OMZET_7 INT, OMZET_8 INT, OMZET_9 INT, OMZET_10 INT, OMZET_11 INT, OMZET_12 INT, OMZET_AVERAGE INT,"
				+ "LOAN_NUMBER TEXT, CIF_NUMBER INT,SOURCE_CODE TEXT, GU_FUP DATETIME, IS_PIPELINE_KP TEXT,"
				+ "ROW_ID TEXT, FLAG_FREEZE INT, PLACE_OF_BIRTH TEXT, NIP TEXT, KARAKTER TEXT"
				//+ ",COORDINATE TEXT
				+ ");" 
				+ "CREATE INDEX IDX_TBL_PROSPECT_PROSPECT_NO ON TBL_PROSPECT (PROSPECT_NO);";

		db.execSQL(sql);

		sql = "CREATE TABLE TBL_PROSPECT_VISIT ("
				+ "PROSPECT_ID INTEGER,"
				+ "VISIT_NO INTEGER, DATE_VISIT_PLAN TEXT,"
				+ "DATE_VISIT_ACTUAL TEXT, JOIN_FIELD_WORK TEXT,"
				+ "OBJECTIVE_OF_VISIT TEXT, RESULT_OF_VISIT TEXT,"
				+ "REMARK TEXT, ENTRY_BY TEXT, ENTRY_DATE DATETIME,"
				+ "UPDATE_BY TEXT, UPDATE_DATE DATETIME,"
				+ "UPLOAD_BY TEXT, UPLOAD_DATE DATETIME,IS_UPLOAD TEXT, IS_DELETED TEXT,"				
				+ "PRIMARY KEY (PROSPECT_ID, VISIT_NO))";

		db.execSQL(sql);
		
		sql = "CREATE TABLE TBL_PROSPECT_PROGRESS ("
				+ "PROSPECT_ID TEXT,"
				+ "PROGRESS_STATUS TEXT,"				
				+ "ENTRY_BY TEXT, ENTRY_DATE DATETIME,"
				+ "UPDATE_BY TEXT, UPDATE_DATE DATETIME,"
				+ "UPLOAD_BY TEXT, UPLOAD_DATE DATETIME, IS_UPLOAD TEXT,"				
				+ "PRIMARY KEY (PROSPECT_ID, PROGRESS_STATUS))";

		db.execSQL(sql);

		sql = "CREATE TABLE TBL_MS_USER_ZIP (POSTAL_CODE TEXT,"
				+ "KODYA_KAB INT, KECAMATAN TEXT, KELURAHAN TEXT,"				
				+ "UPDATE_DATE DATETIME, AO_CODE TEXT)";

		db.execSQL(sql);

		sql = "CREATE TABLE TBL_MS_STANDARD (STD_GROUP TEXT,"
				+ "STD_VALUE TEXT, STD_TEXT TEXT, STD_TYPE TEXT,"				
				+ "UPDATE_DATE DATETIME, IS_ACTIVE TEXT,"
				+ "PRIMARY KEY (STD_GROUP, STD_VALUE))";

		db.execSQL(sql);

		sql = "CREATE TABLE TBL_MAINTENANCE_VISIT ("
				+ "VISIT_ID TEXT PRIMARY KEY,"
				+ "AO_CODE TEXT, UNIT_CODE TEXT,"
				+ "DATE_VISIT TEXT, NO_ACCOUNT TEXT, DEBTOR_NAME TEXT,"
				+ "OBJECTIVE_OF_VISIT TEXT,"
				+ "ENTRY_BY TEXT, ENTRY_DATE DATETIME,"
				+ "UPDATE_BY TEXT, UPDATE_DATE DATETIME,"
				+ "IS_UPLOAD TEXT, UPLOAD_BY TEXT, UPLOAD_DATE DATETIME, IS_DELETED TEXT"
				//+ ",COORDINATE TEXT"
				+ ")";

		db.execSQL(sql);

		sql = "CREATE TABLE TBL_AUDIT(ROW_ID INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "ACT_TYPE TEXT,"
				+ "TABLE_NAME TEXT,"
				+ "PRIMARY_KEY TEXT,"
				+ "FIELD_NAME TEXT,"
				+ "OLD_VALUE TEXT,"
				+ "NEW_VALUE TEXT,"
				+ "UPDATE_BY TEXT,"
				+ "UPDATE_DATE DATETIME)";

		db.execSQL(sql);

		sql = "CREATE TABLE TBL_TRANSACTION_LOG(ROW_ID INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "TRANS_DATE DATETIME, USER_NAME TEXT,"
				+ "TRANS_TYPE TEXT, TRANS_DESC TEXT)";

		db.execSQL(sql);
		
		sql = "CREATE TABLE TBL_MS_USER(USER_ID INTEGER PRIMARY KEY,"
				+ "AO_CODE TEXT, NIP TEXT,"
				+ "UNIT_CODE TEXT, USER_NAME TEXT, PASSWORD TEXT,"
				+ "EMAIL_ADDRESS TEXT, IMEI TEXT, ENCRYPTION_KEY TEXT,"
				+ "LAST_ONLINE DATETIME, LAST_LOGIN DATETIME, TOKEN TEXT, SOURCE_CODE TEXT,AO_TYPE TEXT)";

		db.execSQL(sql);
		
		sql = "CREATE TABLE TBL_MS_HOLIDAY(HOLIDAY_DATE DATETIME PRIMARY KEY,"
				+ "HOLIDAY_NAME TEXT, UPDATE_DATE DATETIME, IS_ACTIVE TEXT)";

		db.execSQL(sql);
		
		sql = "CREATE TABLE TBL_COMMUNICATION(COMM_ID INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "COMM_TYPE TEXT, COMM_DATA TEXT, COMM_DATE DATETIME,"
				+ "PACKET_SIZE INT, TOTAL_ROW INT,"
				+ "DESCRIPTION TEXT)";

		db.execSQL(sql);
		
		sql = "CREATE TABLE TBL_MS_EXTERNAL_MAPPING ("
				+ "EXTERNAL_ID TEXT,"
				+ "CHANNEL_ID TEXT, UPDATE_DATE DATETIME,IS_ACTIVE TEXT,"
				+ "PRIMARY KEY (EXTERNAL_ID, CHANNEL_ID)"				
				+ ")";

		db.execSQL(sql);
		
		sql = "CREATE TABLE TBL_MS_FLAG_FREEZE ("
				+ "FLAG INT,"
				+ "FIELD TEXT, ENABLED TEXT, UPDATE_DATE DATETIME,IS_ACTIVE TEXT,"
				+ "PRIMARY KEY (FLAG, FIELD)"				
				+ ")";

		db.execSQL(sql);

		if (USE_LOCAL_DATA)
			populateSampleData(db);
	}

	private void populateSampleData(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			populatePostalCode(db);
			populateOption(db);				
			createDummyUser(db);				
			db.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();				
		} finally {
			db.endTransaction();
		}
	}	
	
	private void createDummyUser(SQLiteDatabase db) throws Exception {
		String key = "GaFdxT1gO77Oeb/6IeyS4l4y";
		
		Encryptor enc = new Encryptor(
				Encryptor.ENC_AES,
				Encryptor.ALGO_AES, key, App.IV);
		
		UserInfo info = new UserInfo();
		info.setAoCode("00001");
		info.setNip("00001937");
		info.setPassword(enc.encryptText("1234"));
		info.setUnitCode("70");
		info.setUserName("Angga Maulana");
		info.setEmailAddress("angga.maulana@shuba.co.id");
		info.setImei("351895059589398");
		info.setEncryptionKey(key);
		info.setLastOnline(new Date());
		info.setLastLogin(new Date());
		
		UserDB.add(db, info);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		/*
		db.execSQL("DROP TABLE IF EXISTS TBL_MS_USER_ZIP");
		db.execSQL("DROP TABLE IF EXISTS TBL_PROSPECT");
		db.execSQL("DROP TABLE IF EXISTS TBL_PROSPECT_VISIT");
		db.execSQL("DROP TABLE IF EXISTS TBL_PROSPECT_PROGRESS");
		db.execSQL("DROP TABLE IF EXISTS TBL_MS_STANDARD");
		db.execSQL("DROP TABLE IF EXISTS TBL_MAINTENANCE_VISIT");
		db.execSQL("DROP TABLE IF EXISTS TBL_AUDIT");
		db.execSQL("DROP TABLE IF EXISTS TBL_TRANSACTION_LOG");
		db.execSQL("DROP TABLE IF EXISTS TBL_MS_USER");

		onCreate(db);*/
		
		if (oldVersion < 2) {
			String sql = "CREATE TABLE TBL_MS_EXTERNAL_MAPPING ("
					+ "EXTERNAL_ID TEXT,"
					+ "CHANNEL_ID TEXT, UPDATE_DATE DATETIME,IS_ACTIVE TEXT,"
					+ "PRIMARY KEY (EXTERNAL_ID, CHANNEL_ID)"				
					+ ")";

			db.execSQL(sql);
			
			sql = "ALTER TABLE TBL_MS_STANDARD ADD COLUMN IS_ACTIVE TEXT";

			db.execSQL(sql);
			
			sql = "UPDATE TBL_MS_STANDARD SET IS_ACTIVE = '1'";
			
			db.execSQL(sql);
			
			sql = "ALTER TABLE TBL_MS_HOLIDAY ADD COLUMN IS_ACTIVE TEXT";

			db.execSQL(sql);
			
			sql = "UPDATE TBL_MS_HOLIDAY SET IS_ACTIVE = '1'";
			
			db.execSQL(sql);
			
			sql = "ALTER TABLE TBL_PROSPECT ADD COLUMN FLAG_FREEZE INT";

			db.execSQL(sql);
			
			sql = "UPDATE TBL_PROSPECT SET FLAG_FREEZE = 0";
			
			db.execSQL(sql);
			
			sql = "CREATE TABLE TBL_MS_FLAG_FREEZE ("
					+ "FLAG INT,"
					+ "FIELD TEXT, ENABLED TEXT, UPDATE_DATE DATETIME,IS_ACTIVE TEXT,"
					+ "PRIMARY KEY (FLAG, FIELD)"				
					+ ")";

			db.execSQL(sql);
		}
		
		if (oldVersion < 3) {
			String sql = "ALTER TABLE TBL_PROSPECT ADD COLUMN PLACE_OF_BIRTH TEXT";

			db.execSQL(sql);
			
			sql = "UPDATE TBL_PROSPECT SET PLACE_OF_BIRTH = ''";
			
			db.execSQL(sql);
			
			sql = "ALTER TABLE TBL_MS_USER_ZIP ADD COLUMN AO_CODE TEXT";

			db.execSQL(sql);			
						
			String aoCode = getFirstAoCode(db);
			if (aoCode != null) {
				sql = "UPDATE TBL_MS_USER_ZIP SET AO_CODE = '" +aoCode+"'";
				
				db.execSQL(sql);
			}

			sql = "ALTER TABLE TBL_MS_USER ADD COLUMN AO_TYPE TEXT";

			db.execSQL(sql);
			
			sql = "UPDATE TBL_MS_USER SET AO_TYPE = 'L'";
			
			db.execSQL(sql);
		}
		
		if (oldVersion < 4) {			
			String sql = "DROP TABLE TBL_PROSPECT_PROGRESS";

			db.execSQL(sql);
			
			sql = "CREATE TABLE TBL_PROSPECT_PROGRESS ("
					+ "PROSPECT_ID TEXT,"
					+ "PROGRESS_STATUS TEXT,"				
					+ "ENTRY_BY TEXT, ENTRY_DATE DATETIME,"
					+ "UPDATE_BY TEXT, UPDATE_DATE DATETIME,"
					+ "UPLOAD_BY TEXT, UPLOAD_DATE DATETIME, IS_UPLOAD TEXT,"				
					+ "PRIMARY KEY (PROSPECT_ID, PROGRESS_STATUS))";
			
			db.execSQL(sql);
		}
		
		if (oldVersion < 5) {
			String sql = "ALTER TABLE TBL_PROSPECT ADD COLUMN NIP TEXT";

			db.execSQL(sql);
			
			sql = "UPDATE TBL_PROSPECT SET NIP = ENTRY_BY";
			
			db.execSQL(sql);
		}
		
		if (oldVersion < 6) {
			String sql = "ALTER TABLE TBL_PROSPECT ADD COLUMN KARAKTER TEXT";

			db.execSQL(sql);
			
			sql = "UPDATE TBL_PROSPECT SET KARAKTER = ''";
			
			db.execSQL(sql);
		}
		
		//ENHANCEMENT 10 Dec 2014
		if (oldVersion < 7) {
			String sql = "ALTER TABLE TBL_MAINTENANCE_VISIT ADD COLUMN";
		}
	}
	
	private String getFirstAoCode(SQLiteDatabase db) {
		String sql = "SELECT AO_CODE FROM TBL_MS_USER";
		
		Cursor cursor = db.rawQuery(sql, null);
		
		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}		
		
		String result = cursor.getString(0);
		
		cursor.close();
		return result;
	}

	private void populateOption(SQLiteDatabase db) {
		InputStream is = ctx.getResources().openRawResource(R.raw.options);
		BufferedReader br = new BufferedReader(new InputStreamReader(is));
		String readLine = null;

		try {
			while ((readLine = br.readLine()) != null) {
				String[] values = readLine.split("\t");
				if (values.length == 3) {
					//StandardDB.addStandard(db, values[0], values[1], values[2]);
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
			Log.e("Database", e.getMessage());
		}
	}

	private void populatePostalCode(SQLiteDatabase db) {
		/*
		InputStream is = ctx.getResources().openRawResource(R.raw.postal_code);
		BufferedReader br = new BufferedReader(new InputStreamReader(is));
		String readLine = null;

		
		String date = BaseDB.getCurrentDateTime();
		try {
			while ((readLine = br.readLine()) != null) {
				String[] values = readLine.split("\t");
				if (values.length == 4) {
					AreaDB.addPostalCode(db, values[0], values[1], values[2],
							values[3], date, g);
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
			Log.e("Database", e.getMessage());
		}*/
	}
}